BEGIN;

-- partner
CREATE SEQUENCE partner_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table partner (
	id integer not null,
	name varchar(512) not null,
	api_key varchar(128) not null
);

ALTER TABLE partner ALTER COLUMN id SET DEFAULT nextval('partner_id_seq'::regclass);
ALTER TABLE partner ADD CONSTRAINT partner_pk PRIMARY KEY (id);
CREATE UNIQUE INDEX partner_api_key_idx ON partner(api_key);
CREATE UNIQUE INDEX partner_name_idx ON partner(name);


-- theatre
CREATE SEQUENCE theatre_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table theatre (
	id integer not null,
	name varchar(512) not null
);

ALTER TABLE theatre ALTER COLUMN id SET DEFAULT nextval('theatre_id_seq'::regclass);
ALTER TABLE theatre ADD CONSTRAINT theatre_pk PRIMARY KEY (id);
CREATE UNIQUE INDEX theatre_name_idx ON theatre(name);
CREATE UNIQUE INDEX theatre_lower_name_idx ON theatre(lower(name));


-- partner_theatre
CREATE SEQUENCE partner_theatre_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table partner_theatre (
	id integer not null,
	theatre_fk integer not null,
	partner_fk integer not null
);

ALTER TABLE partner_theatre ALTER COLUMN id SET DEFAULT nextval('partner_theatre_id_seq'::regclass);
ALTER TABLE partner_theatre ADD CONSTRAINT partner_theatre_pk PRIMARY KEY (id);
ALTER TABLE partner_theatre ADD CONSTRAINT partner_theatre_partner_fk FOREIGN KEY (partner_fk) REFERENCES partner(id) ON DELETE CASCADE;
ALTER TABLE partner_theatre ADD CONSTRAINT partner_theatre_theatre_fk FOREIGN KEY (theatre_fk) REFERENCES theatre(id) ON DELETE CASCADE;
CREATE UNIQUE INDEX partner_theatre_idx ON partner_theatre(theatre_fk, partner_fk);

-- theatre_hall
CREATE SEQUENCE theatre_hall_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table theatre_hall (
	id integer not null,
	theatre_fk integer not null,
	name varchar(512) not null,
	seats integer not null
);

ALTER TABLE theatre_hall ALTER COLUMN id SET DEFAULT nextval('theatre_hall_id_seq'::regclass);
ALTER TABLE theatre_hall ADD CONSTRAINT theatre_hall_pk PRIMARY KEY (id);
ALTER TABLE theatre_hall ADD CONSTRAINT theatre_hall_theatre_fk FOREIGN KEY (theatre_fk) REFERENCES theatre(id) ON DELETE CASCADE;
CREATE UNIQUE INDEX theatre_hall_name_idx ON theatre_hall(theatre_fk, name);
CREATE UNIQUE INDEX theatre_hall_lower_name_idx ON theatre_hall(theatre_fk, lower(name));
CREATE INDEX theatre_hall_seats_idx ON theatre_hall(seats);


-- show
CREATE SEQUENCE show_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table show (
	id integer not null,
	name varchar(256) not null,
	release_date date not null
);

ALTER TABLE show ALTER COLUMN id SET DEFAULT nextval('show_id_seq'::regclass);
ALTER TABLE show ADD CONSTRAINT show_pk PRIMARY KEY (id);
CREATE INDEX show_name_idx ON show(name);
CREATE INDEX show_lower_name_idx ON show(lower(name));
CREATE INDEX show_release_date_idx ON show(release_date);


-- theatre_hall_show
CREATE SEQUENCE theatre_hall_show_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table theatre_hall_show (
	id integer not null,
	representation_date timestamp not null,
	show_fk integer not null,
	theatre_hall_fk integer not null
);

ALTER TABLE theatre_hall_show ALTER COLUMN id SET DEFAULT nextval('theatre_hall_show_id_seq'::regclass);
ALTER TABLE theatre_hall_show ADD CONSTRAINT theatre_hall_show_pk PRIMARY KEY (id);
ALTER TABLE theatre_hall_show ADD CONSTRAINT theatre_hall_show_show_fk FOREIGN KEY (show_fk) REFERENCES show(id) ON DELETE CASCADE;
ALTER TABLE theatre_hall_show ADD CONSTRAINT theatre_hall_show_theatre_hall_fk FOREIGN KEY (theatre_hall_fk) REFERENCES theatre_hall(id) ON DELETE CASCADE;
CREATE INDEX theatre_hall_show_representation_date_idx ON theatre_hall_show(representation_date);


-- booking
CREATE SEQUENCE booking_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table booking (
	id integer not null,
	name varchar(512) null,
	ticket_no integer not null,
	partner_fk integer not null,
	partner_user_id integer not null,
	representation_fk integer not null,
	issued_date timestamp not null,
	completed boolean default false
);

ALTER TABLE booking ALTER COLUMN id SET DEFAULT nextval('booking_id_seq'::regclass);
ALTER TABLE booking ADD CONSTRAINT booking_pk PRIMARY KEY (id);
ALTER TABLE booking ADD CONSTRAINT booking_partner_fk FOREIGN KEY (partner_fk) REFERENCES partner(id) ON DELETE CASCADE;
CREATE INDEX booking_name_idx ON booking(name);
CREATE INDEX booking_lower_name_idx ON booking(lower(name));
CREATE INDEX booking_partner_user_id_idx ON booking(partner_user_id);
CREATE INDEX booking_completed_idx ON booking(completed);


-- booking_ticket
CREATE SEQUENCE booking_ticket_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE table booking_ticket (
	id integer not null,
	booking_fk integer not null,
	show_fk integer not null,
	seat_no integer not null
);

ALTER TABLE booking_ticket ALTER COLUMN id SET DEFAULT nextval('booking_ticket_id_seq'::regclass);
ALTER TABLE booking_ticket ADD CONSTRAINT booking_ticket_pk PRIMARY KEY (id);
ALTER TABLE booking_ticket ADD CONSTRAINT booking_ticket_booking_fk FOREIGN KEY (booking_fk) REFERENCES booking(id) ON DELETE CASCADE;
ALTER TABLE booking_ticket ADD CONSTRAINT booking_ticket_show_fk FOREIGN KEY (show_fk) REFERENCES show(id) ON DELETE CASCADE;
CREATE INDEX booking_ticket_seat_idx ON booking_ticket(seat_no);


-- api_hash
CREATE TABLE api_hash(
	hash varchar(64) not null,
	requested timestamp not null
);
ALTER TABLE api_hash ADD CONSTRAINT api_hash_pk PRIMARY KEY (hash);
CREATE INDEX api_hash_requested_idx ON api_hash(requested);


-- insert data
insert into theatre (id, name) values (1, 'Hollywood Multiplex');
insert into theatre (id, name) values (2, 'Cinema City Palace');
insert into theatre (id, name) values (3, 'Grand Cinema');
insert into theatre (id, name) values (4, 'Cinema Plazza');

insert into partner (id, name, api_key) values (1, 'partner1', 'apikeypartner1');
insert into partner (id, name, api_key) values (2, 'partner2', 'apikeypartner2');

insert into partner_theatre (id, theatre_fk, partner_fk) values (1, 1, 1);
insert into partner_theatre (id, theatre_fk, partner_fk) values (2, 2, 2);
insert into partner_theatre (id, theatre_fk, partner_fk) values (3, 3, 1);
insert into partner_theatre (id, theatre_fk, partner_fk) values (4, 4, 2);

insert into theatre_hall (id, theatre_fk, name, seats) values (1, 1, 'Hall1', 60);
insert into theatre_hall (id, theatre_fk, name, seats) values (2, 1, 'Hall2', 60);
insert into theatre_hall (id, theatre_fk, name, seats) values (3, 1, 'Hall3', 60);
insert into theatre_hall (id, theatre_fk, name, seats) values (4, 2, 'Hall1', 60);
insert into theatre_hall (id, theatre_fk, name, seats) values (5, 2, 'Hall2', 60);
insert into theatre_hall (id, theatre_fk, name, seats) values (6, 3, 'Hall1', 60);

insert into show (id, name, release_date) values (1, 'Movie1', '2012-12-12');
insert into show (id, name, release_date) values (2, 'Movie2', '2012-12-13');
insert into show (id, name, release_date) values (3, 'Movie3', '2012-12-14');

insert into theatre_hall_show (id, representation_date, show_fk, theatre_hall_fk) values (1, '2012-12-07 12:00:00', 1, 1);
insert into theatre_hall_show (id, representation_date, show_fk, theatre_hall_fk) values (2, '2012-12-07 14:00:00', 1, 1);
insert into theatre_hall_show (id, representation_date, show_fk, theatre_hall_fk) values (3, '2012-12-07 14:00:00', 1, 4);
insert into theatre_hall_show (id, representation_date, show_fk, theatre_hall_fk) values (4, '2012-12-07 14:00:00', 2, 5);


-- get the partner by ID
CREATE OR REPLACE FUNCTION getPartnerById(partnerId integer)
	RETURNS SETOF partner AS $$
DECLARE
	partnerRow partner%ROWTYPE;
BEGIN
	-- search for the id, exact match
	SELECT INTO partnerRow * FROM partner WHERE id = partnerId LIMIT 1;

	RETURN NEXT partnerRow;
END;
$$ LANGUAGE plpgsql;


-- get the theatre hall by ID
CREATE OR REPLACE FUNCTION getTheatreHallById(theatreHallId integer)
	RETURNS SETOF theatre_hall AS $$
DECLARE
	theatreHallRow theatre_hall%ROWTYPE;
BEGIN
	-- search for the id, exact match
	SELECT INTO theatreHallRow * FROM theatre_hall WHERE id = theatreHallId LIMIT 1;

	RETURN NEXT theatreHallRow;
END;
$$ LANGUAGE plpgsql;


-- hash already sent
CREATE OR REPLACE FUNCTION hashExpired(hashValue varchar)
	RETURNS boolean AS $$
DECLARE
	hashRow api_hash%ROWTYPE;
BEGIN
        SELECT INTO hashRow * FROM api_hash where hash = hashValue;

	IF NOT FOUND THEN
		INSERT INTO api_hash (hash, requested) VALUES (hashValue, CURRENT_TIMESTAMP);

		RETURN false;
	END IF;

        RETURN true;
END;
$$ LANGUAGE plpgsql;


-- get all seats
CREATE OR REPLACE FUNCTION getAllSeats(hallId integer, partnerId integer, userId integer, showId integer, startDate timestamp, endDate timestamp, representationID integer)
	RETURNS TABLE(hall_seats integer, booking_completed boolean, booked_seat integer) AS $$
BEGIN
	RETURN QUERY
	SELECT
		theatre_hall.seats,
		booking.completed,
		booking_ticket.seat_no
	FROM
		theatre_hall
		join theatre_hall_show ON (
			theatre_hall_show.theatre_hall_fk = theatre_hall.id
			AND theatre_hall_show.show_fk = showId
			AND theatre_hall_show.id = representationID
		)
		LEFT OUTER JOIN booking ON (
			booking.partner_user_id = userId
			AND booking.partner_fk = partnerId
		)
		LEFT OUTER JOIN booking_ticket ON booking.id = booking_ticket.booking_fk
	WHERE
		theatre_hall.id = hallId
		AND theatre_hall_show.representation_date BETWEEN startDate AND endDate;
END;
$$ LANGUAGE plpgsql;


-- get schedule
CREATE OR REPLACE FUNCTION getSchedule(partnerId integer, startDate timestamp, endDate timestamp)
	RETURNS TABLE(show_id integer, show_name varchar, hall_id integer, hall_name varchar, show_date timestamp, representation_id integer) AS $$
BEGIN
	RETURN QUERY
	SELECT
		show.id,
		show.name,
		theatre_hall.id,
		theatre_hall.name,
		theatre_hall_show.representation_date,
		theatre_hall_show.id
	FROM
		partner_theatre, theatre_hall, theatre_hall_show, show
	WHERE
		partner_theatre.partner_fk = partnerId
		AND theatre_hall.theatre_fk = partner_theatre.theatre_fk
		AND theatre_hall_show.theatre_hall_fk = theatre_hall.id
		AND show.id = theatre_hall_show.show_fk
		AND theatre_hall_show.representation_date BETWEEN startDate AND endDate
	ORDER BY
		theatre_hall_show.representation_date;
END;
$$ LANGUAGE plpgsql;


-- insert unconfirmed booking
CREATE OR REPLACE FUNCTION addUnconfirmedBooking(seatNo integer, partnerId integer, partnerUserId integer, issuedDate timestamp, representationId integer, showId integer, selectedSeats varchar)
	RETURNS boolean AS $$
DECLARE
	bookingId booking.id%TYPE;
	seatNumber integer;
	selectedSeatsArr text[];
BEGIN
	INSERT INTO booking
		(ticket_no, partner_fk, partner_user_id, issued_date, representation_fk)
	VALUES (seatNo, partnerId, partnerUserId, issuedDate, representationId)
	RETURNING id INTO bookingId;

	selectedSeatsArr := string_to_array(selectedSeats, ',');

	IF bookingId > 0 THEN
		FOR seatNumber IN array_lower(selectedSeatsArr, 1) .. array_upper(selectedSeatsArr, 1) LOOP
			INSERT INTO booking_ticket (booking_fk, show_fk, seat_no) VALUES (bookingId, showId, selectedSeatsArr[seatNumber]::Integer);
		END LOOP;
	END IF;

	RETURN true;
END;
$$ LANGUAGE plpgsql;


-- confirm booking
CREATE OR REPLACE FUNCTION confirmBooking(bookingName varchar, userId integer, partnerId integer, representationId integer)
	RETURNS boolean AS $$
BEGIN
	UPDATE booking 
		SET completed = true, name = bookingName
	WHERE
		partner_user_id = userId
		AND completed = false
		AND partner_fk = partnerId
		AND representation_fk = representationId;

	RETURN true;
END;
$$ LANGUAGE plpgsql;


-- delete unconfirmed bookings
CREATE OR REPLACE FUNCTION deleteUnconfirmedBooking(userId integer, partnerId integer, representationId integer)
	RETURNS boolean AS $$
BEGIN
	DELETE FROM booking 
	WHERE
		partner_user_id = userId
		AND completed = 'f'
		AND partner_fk = partnerId
		AND representation_fk = representationId;

	RETURN true;
END;
$$ LANGUAGE plpgsql;


-- get bookings
CREATE OR REPLACE FUNCTION getBookings(representationId integer, partnerId integer, showId integer, userId integer, hallId integer)
	RETURNS TABLE(seat_no integer) AS $$
BEGIN
	RETURN QUERY
	SELECT
		booking_ticket.seat_no
	FROM
		booking_ticket, booking, theatre_hall_show
	WHERE
		booking_ticket.booking_fk = booking.id
		AND booking_ticket.show_fk = theatre_hall_show.show_fk
		AND booking.representation_fk = representationId
		AND booking.partner_fk = partnerId
		AND booking_ticket.show_fk = showId
		AND booking.partner_user_id = userId
		AND theatre_hall_show.theatre_hall_fk = hallId
	ORDER BY
		booking_ticket.seat_no;
END;
$$ LANGUAGE plpgsql;

COMMIT;
